﻿CREATE PROCEDURE [dbo].[spImportOrder]
	@id int,
	@LineNumber int,
	@ImportBatchID int,
	@CustomerID int out,
	@OrderID int out
as
	declare @Message table ( [Message] nvarchar(500) )
	set @OrderID = null
	declare @OwnerID int
	select top 1 @OwnerID = OwnerID from impOrders where id = @id

	-- Check FirstName field.
	if ( select coalesce(FirstName, '') from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing FirstName' )

	-- Check LastName field.
	if ( select coalesce(LastName, '') from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing LastName' )

	-- Check Address1 field.
	if ( select coalesce(Address1, '') from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing Address1' )

	-- Check City field.
	if ( select coalesce(City, '') from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing City' )

	-- Check State field.
	if ( select coalesce(State, '') from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing State' )

	-- Check ZipCode field.
	if ( select coalesce(PostalCode, '') from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing PostalCode' )

	-- Check Country field.
	if ( select coalesce(Country, '') from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing Country' )

	-- Check that the country is valid
	if not exists ( select 1 from impOrders T1 join codeCountry T2 on T1.Country = T2.Name
				where T1.id = @id )
		insert into @Message( [Message] ) select 'Invalid Country ' + Country from impOrders where id = @id

	-- Check Phone field.
	--if ( select coalesce(Phone, '') from impOrders where id=@id ) = ''
	--	insert into @Message( [Message] ) values ( 'Missing Phone' )

	-- Check OrderDate field.
	if ( select OrderDate from impOrders where id=@id ) = ''
		insert into @Message( [Message] ) values ( 'Missing OrderDate' )

	-- Check ShipToFirstName field.
	if ( select coalesce(ShipToFirstName, '') from impOrders where id=@id ) = ''
		update impOrders set ShipToFirstName = FirstName where id = @id

	-- Check ShipToLastName field.
	if ( select coalesce(ShipToLastName, '') from impOrders where id=@id ) = ''
		update impOrders set ShipToLastName = LastName where id = @id

	-- Check ShipToAddress field.
	if ( select coalesce(ShipToAddress1, '') from impOrders where id=@id ) = ''
		update impOrders set ShipToAddress1 = Address1, ShipToAddress2 = Address2, ShipToAddress3 = Address3, ShipToCity = City, ShipToState = [State], ShipToCompany = Company, ShipToCountry = Country, ShipToPostalCode = PostalCode
		where id = @id

	-- Check that the country is valid
	if not exists ( select 1 from impOrders T1 join codeCountry T2 on T1.ShipToCountry = T2.Name
				where T1.id = @id )
		insert into @Message( [Message] ) select 'Invalid ShipTo Country ' + ShipToCountry from impOrders where id = @id

	-- Check QtyOrdered field.
	if ( select QtyOrdered from impOrders where id=@id ) is null
		insert into @Message( [Message] ) values ( 'Missing QtyOrdered' )

	-- Check ItemCode field.
	if ( select ItemCode from impOrders where id=@id ) is null
		insert into @Message( [Message] ) values ( 'Missing ItemCode' )

	-- Check that the ItemCode is valid
	insert into @Message( [Message] ) select 'Invalid ItemCode ' + T1.ItemCode 
	from impOrders T1 
		left outer join tblProduct T2 on T1.ItemCode = T2.ItemCode and T2.OwnerID = T1.OwnerID 
	where T1.id = @id
		and T2.ItemCode is null

	-- Check the UnitPrice field
	--if ( select UnitPrice from impOrders where id=@id ) is null
	--	insert into @Message( [Message] ) values ( 'Missing UnitPrice' )

	-- If a language is specified, make sure that it is a valid language
	if ( select coalesce( Language, '' ) from impOrders where id=@id ) <> ''
		if not exists ( select 1 from impOrders T1 join codeLanguage T2 on T1.Language = T2.Name
				where T1.id = @id )
		insert into @Message( [Message] ) values ( 'Invalid language specified' )

	-- If a promotion is specified, make sure that it is valid
	--if ( select OrderSource from impOrders where id = @id ) is not null
	--	if ( select 1 from impOrders T1 
	--			join tblPromotion T2 on T1.OrderSource = T2.[Description] and T2.OwnerID = T1.OwnerID 
	--			where T1.id = @id) is null 
	--		insert into @Message( [Message] ) values ( 'Invalid Order Source specified' )

	-- Check the PreferredShipMethod
	if exists ( select 1 from impOrders T1
				join impOrders T2 on T1.SourceOrderID = T2.SourceOrderID
				left outer join codeCarrier T3 on T2.PreferredShipMethod = T3.Abbrev
			where T2.PreferredShipMethod is not null
				and T3.CarrierCode is null )
			insert into @Message( [Message] ) values ( 'Invalid ship method' )
			
	-- If the product prices aren't specified then look them up.
	update T2 set UnitPrice = T4.Price, UnitDiscount = 0
	from impOrders T1
		join impOrders T2 on T1.SourceOrderID = T2.SourceOrderID
		join tblProduct T3 on T2.ItemCode = T3.ItemCode and T3.OwnerID = T1.OwnerID
		join tblProductPricing T4 on T3.ProductID = T4.ProductID
		join codeProductPricingClass T5 on T4.ProductPricingClassCode = T5.ProductPricingClassCode
			and T5.Description = 'Default'
			and T5.OwnerID = T1.OwnerID
		left outer join tblPromotion T6 on T4.PromotionID = T6.PromotionID
			and T1.OrderSource = T6.[Description]
			and T6.OwnerID = T1.OwnerID
	where T1.id = @id
		and T2.UnitPrice is null

	-- Update the UnitDiscount where it is null
	update T2 set UnitDiscount = 0
	from impOrders T1
		join impOrders T2 on T1.SourceOrderID = T2.SourceOrderID
	where T1.id = @id
		and T2.UnitDiscount is null

	-- Calculate the product total
	declare @ProductTotal money
	select @ProductTotal = T2.ProductTotal
	from impOrders T1
		join ( select SourceOrderID, SUM( QtyOrdered * (UnitPrice * (1 - UnitDiscount)) ) as ProductTotal 
			from impOrders
			group by SourceOrderID ) as T2 on T1.SourceOrderID = T2.SourceOrderID
	where T1.id = @id

	declare @PaymentMethodRequired bit
	set @PaymentMethodRequired = 0

	-- If a PaidAmount is specified then a payment method is not required.
	if ( select coalesce(PaidAmount, 0) from impOrders where id = @id ) = 0
	begin
		-- If shipping is not specified then require a payment method
		if ( select Shipping from impOrders where id = @id ) is null 
			set @PaymentMethodRequired = 1

		-- If there is a shipping amount then require a payment method
		if ( select Shipping from impOrders where id = @id ) > 0
			set @PaymentMethodRequired = 1

		-- If the product amount > 0 then a payment method is required
		if @ProductTotal > 0
			set @PaymentMethodRequired = 1
	end

	if @PaymentMethodRequired = 1
	begin
		declare @CardNumber varchar(16)
		declare @BankAccountNumber nvarchar(50)
		declare @BankRoutingNumber nvarchar(50)
		select @CardNumber = CardNumber,
			@BankAccountNumber = BankAccountNumber,
			@BankRoutingNumber = BankRoutingNumber
		from impOrders where id = @id

		if @CardNumber <> ''
		begin
			-- There is a credit card number so check the validity of the card number and the existance of the other card data.
	      	if dbo.ValidateCreditCardNumber( @CardNumber ) <> 1
				insert into @Message( [Message] ) values ( 'Invalid credit card number' )

			-- Check card expiration
			if ( select CardExpiration from impOrders where id=@id ) is null
				insert into @Message( [Message] ) values ( 'Missing CardExpiration' )
		end
		else if @BankAccountNumber <> ''
		begin
			if dbo.isdigits( @BankAccountNumber ) = 0 
				insert into @Message( [Message] ) values ( 'Invalid bank account number' )
			else if dbo.ValidateBankRoutingNumber( @BankRoutingNumber ) = 0
				insert into @Message( [Message] ) values ( 'Bank routing number failes checksum' )
		end
		else
			insert into @Message( [Message] ) values ( 'No method of payment specified' )
	end

	-- If no errors have been logged so far
	if not exists ( select 1 from @Message )
	begin
		declare @AddressID int
		declare @PersonID int
		declare @AllowPartialShipments bit

		select @AllowPartialShipments = coalesce(AllowPartialShipments, 0) from tblOwner where OwnerID = (select top 1 OwnerID from impOrders where id = @id)

		if @CustomerID is null
		begin
			-- Try to find an existing matching address
			set @AddressID = null
			select @AddressID = T1.AddressID
				from tblAddress T1
					join impOrders T2 on T1.Address1 = T2.Address1 
						and T1.Address2 = T2.Address2
						and T1.PostalCode = T2.PostalCode
				where T2.id = @id

			-- If there is no existing matching address record then create one
			if @AddressID is null
			begin
				insert into tblAddress ( Address1, Address2, Address3, City, State, CountryID, PostalCode )
				select Address1, Address2, Address3, City, State, T2.CountryID, PostalCode
				from impOrders T1
					join codeCountry T2 on T1.Country = T2.Name
				where T1.id = @id

				set @AddressID = SCOPE_IDENTITY()
			end

			-- Try to find an existing matching person record
			set @PersonID = null
			select @PersonID = T1.PersonID
			from tblPerson T1
				join impOrders T2 on T1.Name = dbo.MakeName(T2.FirstName, T2.LastName)
				left outer join tblContactPoint T3 on T1.PersonID = T3.PersonID
					and T3.ContactPointTypeCode = 'E'
					and T3.Value = T2.EMail
				left outer join tblContactPoint T4 on T1.PersonID = T4.PersonID
					and T4.ContactPointTypeCode = 'P'
					and T4.Value = dbo.CleanupPhone(T2.Phone)
				left outer join tblAddress T5 on T1.AddressID = T5.AddressID
					and T5.AddressID = @AddressID
			where T2.id = @id
				and ( T3.ContactPointID is not null or T4.ContactPointID is not null or T5.AddressID is not null )

			-- If there is a matching person record then update it with the new information
			-- Otherwise create a new person record
			if @PersonID is not null 
			begin
				update tblPerson set AddressID = @AddressID
				where PersonID = @PersonID
			end
			else
			begin
				insert into tblPerson ( FirstName, LastName, Name, AddressID, LanguageID )
				select T1.FirstName, T1.LastName, dbo.MakeName(T1.FirstName, T1.LastName), @AddressID,
					coalesce( T2.LanguageID, 1 )
				from impOrders T1
					left outer join codeLanguage T2 on T1.Language = T2.Name

				set @PersonID = SCOPE_IDENTITY()
			end

			set @CustomerID=null
			select @CustomerID = CustomerID
			from tblCustomer
			where PersonID = @PersonID
				and OwnerID = @OwnerID

			-- Create a new customer record
			if @CustomerID is null
			begin
				insert into tblCustomer ( AllowCleaning, AllowEmail, AllowMail, AllowPhone, AllowRent, DoNotProcess, ImportDate, OwnerID, PersonID )
				values ( 1, 1, 1, 1, 1, 0, GETDATE(), @OwnerID, @PersonID )

				set @CustomerID = SCOPE_IDENTITY()
			end
		end
		else
		begin
			-- If the customer record is passed, then update the person and address information
			select @PersonID = T1.PersonID,
				@AddressID = T2.AddressID
			from tblCustomer T1
				join tblPerson T2 on T1.PersonID = T2.PersonID

			-- If the passed address doesn't match to the address of the customer
			-- then create a new address record
			if not exists ( select 1
				from tblAddress T1,
					impOrders T2
				where T1.AddressID = @AddressID
					and T2.id = @id
					and T1.Address1 = rtrim(coalesce(T2.Address1, ''))
					and T1.Address2 = rtrim(coalesce(T2.Address2, ''))
					and T1.City = RTRIM(coalesce(T2.City, ''))
					and T1.PostalCode = RTRIM(coalesce(T2.PostalCode, ''))
				)
			begin
				insert into tblAddress ( Address1, Address2, Address3, City, State, CountryID, PostalCode )
				select Address1, Address2, Address3, City, State, T2.CountryID, PostalCode
				from impOrders T1
					join codeCountry T2 on T1.Country = T2.Name
				where id = @id

				set @AddressID = SCOPE_IDENTITY()

				update tblPerson set AddressID = @AddressID where PersonID = @PersonID
			end
		end
					
		-- Create the Email ContactPoint record for the customer		
		insert into tblContactPoint ( ContactPointTypeCode, Value, PersonID )
		select 'E', T1.Email, @PersonID
		from impOrders T1
		where id=@id
			and coalesce(T1.email, '') <> ''
			and not exists ( select 1 from tblContactPoint where ContactPointTypeCode='E'
				and PersonID=@PersonID
				and Value=T1.EMail)

		-- Create the Phone ContactPoint record for the customer		
		insert into tblContactPoint ( ContactPointTypeCode, Value, PersonID )
		select 'P', dbo.CleanupPhone(T1.Phone), @PersonID
		from impOrders T1
		where id=@id
			and coalesce(T1.Phone, '') <> ''
			and not exists ( select 1 from tblContactPoint where ContactPointTypeCode='P'
				and PersonID=@PersonID
				and Value=dbo.CleanupPhone(T1.Phone))

		-- Locate a matching account
		declare @AccountID int
		set @AccountID = null
		select @AccountID = T1.AccountID
		from tblAccount T1
			join impOrders T2 on T1.CardNumber = T2.CardNumber
		where T2.id = @id
			and T1.OwnerID = T2.OwnerID

		if @AccountID is null
		select @AccountID = T1.AccountID
		from tblAccount T1
			join impOrders T2 on T1.BankAccountNumber = T2.BankAccountNumber
				and T1.BankRoutingNumber = T2.BankRoutingNumber
		where T2.id = @id
			and T1.OwnerID = T2.OwnerID

		if @AccountID is null
		begin
			insert into tblAccount( CreateDate, CurrencyID, CardNumber, CardExpiration, BankAccountNumber, BankRoutingNumber, OwnerID, AddressID, PersonID, AccountTypeCode )
			select GETDATE(), 1, dbo.Encrypt(CardNumber), CardExpiration, dbo.Encrypt(BankAccountNumber), BankRoutingNumber, OwnerID, @AddressID, @PersonID, 'R'
			from impOrders
			where id=@id

			set @AccountID = SCOPE_IDENTITY()
		end
		else
			update T1 set CardExpiration = T2.CardExpiration
			from tblAccount T1,
				impOrders T2
			where T1.AccountID = @AccountID
				and T2.id = @id

		-- Try to find an existing matching ShipTo address
		set @AddressID = null
		select @AddressID = T1.AddressID
			from tblAddress T1
				join impOrders T2 on T1.Address1 = T2.ShipToAddress1 
					and T1.Address2 = T2.ShipToAddress2
					and T1.PostalCode = T2.ShipToPostalCode
			where T2.id = @id

		-- If there is no existing matching address record then create one
		if @AddressID is null
		begin
			insert into tblAddress ( Address1, Address2, Address3, City, State, CountryID, PostalCode )
			select ShipToAddress1, ShipToAddress2, ShipToAddress3, ShipToCity, ShipToState, T2.CountryID, ShipToPostalCode
			from impOrders T1
				join codeCountry T2 on T1.ShipToCountry = T2.Name
			where id = @id

			set @AddressID = SCOPE_IDENTITY()
		end

		-- Try to find an existing matching person record
		set @PersonID = null
		select @PersonID = PersonID
		from tblPerson T1
			join impOrders T2 on T1.Name = dbo.MakeName(T2.ShipToFirstName, T2.ShipToLastName)
		where T1.AddressID = @AddressID
			and T2.id = @id

		-- If there is no matching person record create a new person record
		if @PersonID is null 
		begin
			insert into tblPerson ( FirstName, LastName, Name, AddressID, LanguageID )
			select T1.ShipToFirstName, T1.ShipToLastName, dbo.MakeName(T1.ShipToFirstName, T1.ShipToLastName), @AddressID,
				coalesce( T2.LanguageID, 1 )
			from impOrders T1
				left outer join codeLanguage T2 on T1.Language = T2.Name
			where T1.id = @id

			set @PersonID = SCOPE_IDENTITY()
		end

		insert into tblOrderHeader( CustomerID, 
			OrderDate, 
			ProjShipDate, 
			OrderTypeCode,
			ProductTotal,
			Shipping, 
			Taxes,
			OrderTotal,
			ImportDate,
			PrevOrderID,
			HoldReasonCode,
			AccountID,
			ShipTo_PersonID,
			ShipTo_CompanyName,
			ShipTo_AddressID,
			OrderSourceCode,
			PromotionID,
			OrderStatusCode,
			PrePaid,
			PreferredCarrierCode,
			OwnerID,
			ImportBatchID )
		select @CustomerID,
			T1.OrderDate,
			floor(cast(GETDATE() as float)),
			'R',
			@ProductTotal,
			T1.Shipping,
			0,
			0,
			GETDATE(),
			null,
			null,
			@AccountID,
			@PersonID,
			T1.ShipToCompany,
			@AddressID,
			'I',
			T3.PromotionID,
			case when @PaymentMethodRequired IS null then 'P' else 'E' end,
			case when @PaymentMethodRequired IS null then 0 else 1 end,
			T4.CarrierCode,
			T1.OwnerID,
			@ImportBatchID
		from impOrders T1
			left outer join tblPromotion T3 on T1.OrderSource = T3.[Description]
				and T3.OwnerID = T1.OwnerID
			left outer join codeCarrier T4 on T1.PreferredShipMethod = T4.Abbrev
		where T1.id = @id

		set @OrderID = SCOPE_IDENTITY()

		-- Create the order detail records
		insert into tblOrderDetail ( OrderID, ProductID, Qty, UnitPrice, Discount, UnitShipping, PreferredCarrierCode, ImportDate )
		select @OrderID, T3.ProductID, T2.QtyOrdered, T2.UnitPrice, T2.UnitDiscount, 0, T4.CarrierCode, GETDATE()
		from impOrders T1
			join impOrders T2 on T1.SourceOrderID = T2.SourceOrderID
			join tblProduct T3 on T2.ItemCode = T3.ItemCode and T1.OwnerID = T3.OwnerID
			left outer join codeCarrier T4 on T1.PreferredShipMethod = T4.Abbrev
		where T1.id = @id
			and coalesce( T1.PreferredShipMethod, '') = coalesce( T4.Abbrev, '' )

		-- Create the OrderTax records
		insert into tblOrderTax ( OrderID, TaxRateID, Amount )
		select @OrderID, T6.TaxRateID, dbo.CalcTax(T6.TaxRateID, @ProductTotal) 
				+ case when T6.TaxShipping = 1 then dbo.CalcTax(T6.TaxRateID, T1.Shipping) else 0 end
		from impOrders T1
			join codeCountry T2 on T1.ShipToCountry = T2.Name
			join tblPostalCode T3 on T1.ShipToPostalCode = T3.PostalCode
				and T2.CountryID = T3.CountryID
			join tblPostalCodeTaxZone T4 on T3.PostalCodeID = T4.PostalCodeID
			join tblTaxZone T5 on T4.TaxZoneID = T5.TaxZoneID
			join tblTaxRate T6 on T5.TaxZoneID = T6.TaxRateID

		update T1 set Taxes = T2.Taxes
		from tblOrderHeader T1
			join ( select OrderID, SUM( Amount ) as Taxes
				from tblOrderTax
				group by OrderID ) as T2 on T1.OrderID = T2.OrderID
		where T1.OrderID = @OrderID

		update tblOrderHeader set OrderTotal = ProductTotal + Taxes + Shipping
		where OrderID = @OrderID

		declare @PaidAmount money
		select @PaidAmount = PaidAmount from impOrders where id = @id 
		declare @OrderTotal money
		select @OrderTotal = OrderTotal from tblOrderHeader where OrderID = @OrderID

		if abs(@PaidAmount - @OrderTotal) > 0.01 and @PaymentMethodRequired = 0 
		begin
			declare @ErrorMessage varchar(max)
			set @ErrorMessage = 'Paid amount of ' + cast(@PaidAmount as varchar) + ' doesn''t match to order total of ' + cast(@OrderTotal as varchar)
			RAISERROR ( @ErrorMessage, 18, -1 )
		end

		exec spAllocateInventory @OrderID, @AllowPartialShipments

		exec spBuildSearchKeys @CustomerID

	end

	delete from T2
	from impOrders T1
		join impOrders T2 on T1.SourceOrderID = T2.SourceOrderID
	where T1.id = @id

	select @LineNumber as LineNumber, [Message] from @Message

	if ( select COUNT(*) from @Message ) = 0 return 0
	else return 1